package cn.rengy.web.framework.sql;

import java.io.OutputStream;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import cn.rengy.web.framework.cache.CacheService;
import cn.rengy.web.framework.cache.LocalCacheManager;
import cn.rengy.web.framework.util._Constant.SqlParsingEngine;
import cn.rengy.web.framework.util._Constant.SqlType;

@Service
public class DbService {
	private static Logger logger = LoggerFactory.getLogger(DbService.class);
	@Autowired
	private Dao dao;
	@Autowired
	private CacheManager cacheManager;
	@Autowired
	private LocalCacheManager localCacheManager;
	
	@Autowired
	private CacheService cacheService;
	
	//@Cacheable(value="sqlCache",key="#sqlname")
	public String getSqlByName(String sqlname){
		SqlEntity sqlEntity=getSqlMapByName(sqlname);
		return sqlEntity.getSql_value();
	}
	String sql="select * from sys_sql where sql_name=?";
	public SqlEntity getSqlMapByName(String sqlname){
		return cacheService.getLocalCache("sqlCache", sqlname, new Callable<SqlEntity>() {
	        @Override
	        public SqlEntity call() throws Exception {
	        	SqlEntity sqlEntity=dao.queryForBean(sql, SqlEntity.class, sqlname);
	        	if(sqlEntity==null) {
	        		return null;
	        	}
	        	String sql_value=sqlEntity.getSql_value();
	        	int start  = sql_value.indexOf("${");
	        	//int start1 = sql_value.indexOf("#{");
	        	//String engine_type=SqlParsingEngine.none.name();
	            if (start > -1 ) {
	            	sqlEntity.setEngine_type(SqlParsingEngine.DIY.name());
	            	
	            }else {
	            	sqlEntity.setEngine_type(SqlParsingEngine.none.name());
	            }
	            
	            if (sql_value.indexOf(":") > -1 ) {
	            	sqlEntity.setSql_type(SqlType.named.name());
	            }else {
	            	sqlEntity.setSql_type(SqlType.prepared.name());
	            }
	        	return sqlEntity;
	        }
	    });
	}
	/**
	 * 查询如：List<Long> 
	 * @param <T>
	 * @param sqlid
	 * @param elementType
	 * @param args
	 * @return
	 */
	public <T> List<T> queryForSingleColumnList(String sqlid, Class<T> elementType,Object... args){
		String sqlValue=getSqlByName(sqlid);
		return  dao.querySingleColumnForList(sqlValue,elementType,args);
	}
	public <T> List<T> querySingleColumnForListMapParameter(String sqlid, Map<String,Object> paramMap, Class<T> elementType){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		return dao.queryNamedSingleColumnForList(sqlValue, sqlParameterSource, elementType);
	}
	public List<Map<String,Object>> queryForList(String sqlid,Object... args){
		String sqlValue=getSqlByName(sqlid);
		List<Map<String,Object>> list=dao.queryForList(sqlValue,args);
		//TODO transcode会造成递归引用?
		//objectTranscoder.transcode(sqlid, list);
		return list;
	}
	public List<Map<String,Object>> queryForListMapParameter(String sqlid,Map<String,Object> paramMap){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		
		List<Map<String,Object>> list=dao.queryNamedForList(sqlValue,sqlParameterSource);
		//objectTranscoder.transcode(sqlid, list);
		return list;
	}
	public List<Map<String,Object>> queryForListBeanParameter(String sqlid,Object paramBean){
		BeanPropertySqlParameterSource sqlParameterSource=new BeanPropertySqlParameterSource(paramBean);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		return dao.queryNamedForList(sqlValue, sqlParameterSource);
	}
	public <T> List<T> queryBeanForListBeanParameter(String sqlid, Object paramBean, Class<T> elementType){
		BeanPropertySqlParameterSource sqlParameterSource=new BeanPropertySqlParameterSource(paramBean);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		return dao.queryNamedBeanForList(sqlValue, sqlParameterSource, elementType);
	}
	public <T> List<T> queryForBeanList(String sqlid, Class<T> elementType, Object... args){
		String sqlValue=getSqlByName(sqlid);
		List<T> list = dao.queryBeanForList(sqlValue, elementType, args);
		return list;
	}
	public <T> List<T> queryBeanForListMapParameter(String sqlid, Map<String,Object> paramMap, Class<T> elementType){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		return dao.queryNamedBeanForList(sqlValue, sqlParameterSource, elementType);
	}
	
	
	
	public Map<String,Object> queryForMap(String sqlid,Object... args){
		String sqlValue=getSqlByName(sqlid);
		Map<String,Object> map =dao.queryForMap(sqlValue, args);
		//objectTranscoder.transcode(sqlid, map);
		//System.out.println(map.getClass().getName());
		return map;
	}
	
	
	public int update(String sqlid,Object... args){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		int i= dao.update(sqlValue, args);
		removeCache(sqlEntity,args);
		return i;
	}
	
	
	
	public int updateBeanParameter(String sqlid,Object paramBean){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		BeanPropertySqlParameterSource beanPropertySqlParameterSource=new BeanPropertySqlParameterSource(paramBean);
		
		String engine_type=sqlEntity.getEngine_type();
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,beanPropertySqlParameterSource);
		}
		int i= dao.updateNamed(sqlValue, beanPropertySqlParameterSource);
		removeCache( sqlEntity, beanPropertySqlParameterSource);
		return i;
	}
	
	public <T> T queryForBean(String sqlid,Class<T> elementType,Object... args){
		if(logger.isDebugEnabled()){
			logger.debug("sqlid:{},参数:{}",sqlid,Arrays.toString(args));
		}
		String sqlValue=getSqlByName(sqlid);
		T t=null;
		try{
			t=dao.queryForBean(sqlValue, elementType, args);
		}catch(EmptyResultDataAccessException e){}
		return t;
	}
	public <T> T queryForBeanMapParameter(String sqlid,Map<String,Object> paramMap,Class<T> elementType){
		String sqlValue=getSqlByName(sqlid);
		//T t=null;
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		//try{
		return dao.queryNamedForBean(sqlValue, sqlParameterSource, elementType);
		//}catch(EmptyResultDataAccessException e){}
		//return t;
	}
	public Map<String,Object> queryForMapParameter(String sqlid,Map<String,Object> paramMap){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		//String engine_type=(String)sql.get("engine_type");
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		Map<String,Object> map=dao.queryNamedForMap(sqlValue, sqlParameterSource);
		//objectTranscoder.transcode(sqlid, map);
		return map;
	}
	public int updateMapParameter(String sqlid,Map<String,Object> paramMap){
		if(logger.isDebugEnabled()&&paramMap!=null){
			logger.debug("sqlid:{},参数:{}",sqlid,paramMap.toString());
		}
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		if(sqlEntity==null) {
			logger.warn("sql:{}查询为空",sqlid);
			return 0;
		}
		String sqlValue=sqlEntity.getSql_value();
		
		String engine_type=sqlEntity.getEngine_type();
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		int i=dao.updateNamed(sqlValue, sqlParameterSource);
		if(logger.isDebugEnabled()){
			logger.debug("{} update result:{}",sqlid,i);
		}
		removeCache( sqlEntity, sqlParameterSource);
		return i;
	}
	@Transactional(rollbackFor = Exception.class)
	public int updates(String sqlid,Map<String,Object> paramMap){
		List<Map<String,Object>> list=this.queryForList("select_sqllist_likename", sqlid+"%");
		Assert.notNull(list, "sql must not be null");
		int i=0;
		for(Map<String,Object> map:list){
			String sqlname=(String)map.get("sql_name");
			//String sqlvalue=(String)map.get("sql_value");
			i=this.updateMapParameter(sqlname, paramMap);
		}
		return i;
	}
	@Transactional(rollbackFor = Exception.class)
	public int updates(String sqlid,Object... args){
		List<Map<String,Object>> list=this.queryForList("select_sqllist_likename", sqlid+"%");
		Assert.notNull(list, "sql must not be null");
		int i=0;
		for(Map<String,Object> map:list){
			String sqlname=(String)map.get("sql_name");
			//String sqlvalue=(String)map.get("sql_value");
			i+=this.update(sqlname, args);
		}
		return i;
	}
	public Number updateReturnGeneratedKeys(String sqlid,Object... args){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		Number i= dao.updateReturnGeneratedKeys(sqlValue, args);
		removeCache(sqlEntity, args);
		return i;
	}
	/**
	 * 返回更新记录主键
	 * @param sqlid
	 * @param paramMap
	 * @return
	 */
	public Number updateReturnGeneratedKeys(String sqlid,Map<String,Object> paramMap){
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		
		String engine_type=sqlEntity.getEngine_type();
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		Number i= dao.updateNamedReturnGeneratedKeys(sqlValue, sqlParameterSource);
		removeCache( sqlEntity, sqlParameterSource);
		return i;
	}
	private void removeCache(SqlEntity sqlEntity,Object parameter) {
		Integer is_cache=sqlEntity.getIs_cache();
		if(is_cache!=null&&is_cache.intValue()==1){
			String cache_name=sqlEntity.getCache_name();
			String cache_operate=sqlEntity.getCache_operate();
			String cache_type=sqlEntity.getCache_type();
			String sqlType=sqlEntity.getSql_type();
			CacheManager cacheManager=null;
			if("local".equals(cache_type)) {
				cacheManager=this.localCacheManager.getCacheManager();
			}else {
				cacheManager=this.cacheManager;
			}
			Cache cache=cacheManager.getCache(cache_name);
			if("evict".equals(cache_operate)){
				if(parameter instanceof SqlParameterSource) {
					//SqlParameterSource _parameter=(SqlParameterSource)parameter;
					String cache_column=sqlEntity.getCache_key_param();
					Object keyObj=((SqlParameterSource)parameter).getValue(cache_column);
					if(keyObj instanceof Collection){
						Iterator<Object> it=((Collection) keyObj).iterator();
						while(it.hasNext()){
							Object key=it.next();
							cache.evict(key.toString());
							logger.info("移除缓存,name={},key={}",cache_name,key);
						}
					}else{
						logger.info("移除缓存,name={},key={}",cache_name,keyObj);
						cache.evict(keyObj.toString());
					}
				}else if(sqlType.equals(SqlType.prepared.name())) {
					Integer cache_key_index=sqlEntity.getCache_key_index();
					Object[] args=(Object[])parameter;
					if(args!=null&&args.length>0) {
						Object keyObj=args[cache_key_index];
						cache.evict(keyObj);
						logger.info("移除缓存,name={},key={}",cache_name,keyObj);
					}
				}
			}else if("clear".equals(cache_operate)){
				cache.clear();
				logger.info("清空缓存：{}",cache_name);
			}
		}
	}
	/**
	 * 批量更新
	 * @param sqlid
	 * @param batchArgs
	 * @return
	 */
	public int[] batchUpdate(String sqlid,List<Object[]> batchArgs){
		String sqlValue=getSqlByName(sqlid);
		return this.dao.batchUpdate(sqlValue, batchArgs);
	}
	/**
	 * 
	 * @param sqlid
	 * @param valueMaps
	 * @return
	 */
	public int[] batchUpdate(String sqlid,Map<String, Object>[] valueMaps){
		SqlParameterSource[] sqlParameterSource =this.createBatch(valueMaps);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		//String engine_type=sqlEntity.getEngine_type();
		//具名批量update不支持可变参数
//		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
//			for(SqlParameterSource sps:sqlParameterSource){
//				
//				sqlValue=ParseSqlUtils.parseSql(sqlValue,sps);
//				
//			}
//		}
		return this.dao.batchNamedUpdate(sqlValue, sqlParameterSource);
	}
	public int[] batchUpdateBeanParameter(String sqlid,Collection<?> candidates){
		SqlParameterSource[] sqlParameterSource =this.createBatch(candidates);
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		//具名批量update不支持可变参数
		return this.dao.batchNamedUpdate(sqlValue, sqlParameterSource);
	}
	public <T> T querySingleColumn(String sqlid,Class<T> requiredType,Object... args){
		String sqlValue=getSqlByName(sqlid);
		T t=null;
		try{
			t=dao.querySingleColumn(sqlValue, requiredType, args);
		}catch(EmptyResultDataAccessException e){}
		return t;
	}
	public <T> T querySingleColumn(String sqlid,Map<String,Object> paramMap,Class<T> requiredType){
		SqlEntity sqlEntity=this.getSqlMapByName(sqlid);
		String sqlValue=sqlEntity.getSql_value();
		String engine_type=sqlEntity.getEngine_type();
		_MapSqlParameterSource sqlParameterSource=new _MapSqlParameterSource(paramMap);
		if(SqlParsingEngine.DIY.toString().equals(engine_type)) {
			sqlValue=ParseSqlUtils.parseSql(sqlValue,sqlParameterSource);
		}
		return dao.queryNamedSingleColumn(sqlValue,sqlParameterSource,requiredType);
	}
	/**
	 * 更新blob字段
	 * @param sqlid
	 * @param args
	 */
	public void updateBlobAsBytes(String sqlid,Object... args){
		String sqlValue=getSqlByName(sqlid);
		this.dao.updateBlobAsBytes(sqlValue, args);
	}
	/**
	 * 更新blob字段
	 * @param sqlid
	 * @param paramIndex
	 * @param contentLength
	 * @param args
	 */
	public void updateBlobAsBinaryStream(String sqlid,int paramIndex,int contentLength,Object... args){
		String sqlValue=getSqlByName(sqlid);
		this.dao.updateBlobAsBinaryStream(sqlValue, paramIndex,contentLength, args);
	}
	public void update2BlobAsBinaryStream(String sqlid,int paramIndex,int contentLength,int param2Index,int content2Length,Object... args){
		String sqlValue=getSqlByName(sqlid);
		this.dao.update2BlobAsBinaryStream( sqlValue, paramIndex, contentLength, param2Index, content2Length, args);
	}
	public void getBlobAsBinaryStream(OutputStream outputStream,String sqlid,Object... args){
		String sqlValue=getSqlByName(sqlid);
		this.dao.getBlobAsBinaryStream(outputStream, sqlValue, args);
	}
	public byte[] getBlobAsBytes(String sqlid,Object... args){
		String sqlValue=getSqlByName(sqlid);
		return this.dao.getBlobAsBytes(sqlValue,args);
	}
	
	@SuppressWarnings("unchecked")
	private  SqlParameterSource[] createBatch(Collection<?> candidates) {
		SqlParameterSource[] batch = new SqlParameterSource[candidates.size()];
		int i = 0;
		for (Object candidate : candidates) {
			batch[i] = (candidate instanceof Map ? new _MapSqlParameterSource((Map<String, ?>) candidate) :
					new BeanPropertySqlParameterSource(candidate));
			i++;
		}
		return batch;
	}

	
	private  SqlParameterSource[] createBatch(Map<String, ?>[] valueMaps) {
		SqlParameterSource[] batch = new SqlParameterSource[valueMaps.length];
		for (int i = 0; i < valueMaps.length; i++) {
			batch[i] = new _MapSqlParameterSource(valueMaps[i]);
		}
		return batch;
	}
}
